/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author L.E.S
 */
public class DBGunReplicas implements IFDBGunReplicas
{
    private  Connection con;
    /** Creates a new instance of DBGunReplicas */
    public DBGunReplicas()
    {
      con = DBConnection.getInstance().getDBcon();
    }

    public int deleteGunReplica(String barCode)
    {
        int rc=-1;

        String query="DELETE FROM GunReplicas WHERE barCode = '" +
                        barCode + "'";
        System.out.println(query);
        try{ // delete from GunReplica
                con.setAutoCommit(false);
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                rc = stmt.executeUpdate(query);
                stmt.close();
                con.commit();
                con.setAutoCommit(true);
        }//slut try
        catch(Exception ex){
                System.out.println("Delete exception in GunReplicas db: "+ex);

                try{
                    rc=-1;
                    con.rollback();
                    con.setAutoCommit(true);
                }
                catch(Exception ex1)
                {
                    System.out.println("Error rolling back" + ex1);
                }
        }
        return(rc);
    }

    public GunReplicas findGunReplicas(String barCode, boolean retriveAssociation)
    {
        String wClause = "  Product.barcode = '" + barCode + "'";
        return singleWhere(wClause, retriveAssociation);
    }

    public ArrayList<GunReplicas> getAllGunReplicas(boolean retriveAssociation) {
        return miscWhere("", retriveAssociation);
    }

    public int insertGunReplica(GunReplicas gun)
    {
        //call to get the next GunReplica Barcode
        int nextBarCode = GetMax.getMaxId("Select max(barCode) from GunReplicas");
        nextBarCode = nextBarCode + 1;
        System.out.println("next GunReplica Barcode = " +  nextBarCode);

        int rc = -1;
	String query="INSERT INTO GunReplicas(barCode, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, maxStock, currentStock, supplierID)  VALUES('"+
	  	nextBarCode + "','" +
                gun.getName()  + "',"  +
		gun.getPurchasePrice()  + ", " +
                gun.getSalesPrice() + ", " +
                gun.getRentPrice() + ", '" +
                gun.getCountryOfOrigin() + "'," +
				gun.getMinStock() + ", "+
				gun.getMaxStock() + ", "+
				gun.getCurrentStock() + ", " +
                gun.getSupplier().getSupplierID() + ")";

	String query1 = "INSERT INTO GunReplicas(fabric, calibre) VALUES( '"+
				nextBarCode + "','" +
				gun.getFabric() + "','" +
				 gun.getCalibre() + "','" +
				 " WHERE barcode = '" + gun.getBarCode() + "'";

	System.out.println("insert : " + query + query1);
       try{ // insert new GunReplica
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("GunReplicaen ikke oprettet");
          System.out.println("Insert exception in GunReplicas db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
        return(rc);
    }


    public int updateGunReplica(GunReplicas gun) {
            GunReplicas gunObj  = gun;
            int rc=-1;

            String query="UPDATE GunReplica SET "+
                      "name ='"+ gunObj.getName() +"', "+
                      "Purchase price ="+ gunObj.getPurchasePrice() +
                      ", Sales price ="+ gunObj.getSalesPrice() +
                      ", Rent price ="+ gunObj.getRentPrice() +
                      ", Country of origin ='"+ gunObj.getCountryOfOrigin() + "', " +
                      ", Min stock ="+ gunObj.getMinStock() +
					  ", Max stock ="+ gunObj.getMaxStock() +
					  ", Current stock ="+ gunObj.getCurrentStock() +
					  ", Supplier ="+ gunObj.getSupplier() +
                      ",  WHERE barCode = '"+ gunObj.getBarCode() + "'";

			String query1 = "UPDATE GunReplicas SET "
				+ "Fabric ='"+ gunObj.getFabric() + "' " +
				"Calibre ='"+ gunObj.getCalibre() + "' " +
				 " WHERE barCode = '"+ gunObj.getBarCode() + "'";
 System.out.println("Update query:" + query + query1);

            try{ // update GunReplica
                    Statement stmt = con.createStatement();
                    stmt.setQueryTimeout(5);
                    rc = stmt.executeUpdate(query);

                    stmt.close();
            }//slut try
            catch(Exception ex){
                    System.out.println("Update exception in customer db: "+ex);
            }
            return(rc);
    }

    //private methods
    //michWere is used whenever we want to select more than one gunReplica
    private ArrayList<GunReplicas> miscWhere(String wClause, boolean retriveAssociation)
    {
        ResultSet results;
	ArrayList<GunReplicas> list = new ArrayList<GunReplicas>();
        String query =  buildQuery(wClause);
        try{ // read the GunReplicas from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while(results.next() )
            {
	     	 GunReplicas gunObj = new GunReplicas();
		 gunObj = buildGunReplica(results);
                 list.add(gunObj);
            }//end while
            stmt.close();
             if(retriveAssociation)
             {   //The supplier and country is to be build as well
                 for(GunReplicas gunObj : list){
                   
					 DBSupplier DBSupplier = new DBSupplier();
					gunObj.setSupplier(DBSupplier.findSupplier(results.getInt("SupplierID"), false));
					 DBCountry dbCount = new DBCountry();
					 gunObj.setCountryOfOrigin(results.getString("countryOfOrigin"));

                 }
                }//end if
        }//slut try
        catch(Exception e)
        {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
            return list;
    }

    //Singelwhere is used when we only select one gunReplica
    private GunReplicas singleWhere(String wClause, boolean retriveAssociation)
    {
        ResultSet results;
        GunReplicas gunObj = new GunReplicas();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try{ // read the customer from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() )
            {
                gunObj = buildGunReplica(results);
                //assocaition is to be build
                stmt.close();
                if(retriveAssociation)
                {  
					 int supplierID =gunObj.getSupplier().getSupplierID();
                     DBSupplier dbSupplier = new DBSupplier();
                     Supplier supplier = dbSupplier.findSupplier(supplierID, false);
                     gunObj.setSupplier(supplier);
                     String countryCode = gunObj.getCountryOfOrigin();
                     DBCountry dbCount = new DBCountry();
                     String countryName = dbCount.getCountryName(countryCode);
                     gunObj.setCountryOfOrigin(countryName);
                }
            }
            else{ //no gunReplica was found
                gunObj = null;
            }
        }//end try
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return gunObj;
    }
    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT product.barcode,name, puchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, maxStock, currentStock, fabric, calibre, supplier, type FROM Product, GunReplicas";

            if (wClause.length()>0)
                    query=query+" WHERE "+ wClause;

            return query;
    }
    //method to build an gunReplica object
    private GunReplicas buildGunReplica(ResultSet results)
    {
        GunReplicas gunObj = new GunReplicas();
        try{ // the columns from the table product  are used
			gunObj.setBarCode(results.getString("barcode"));
            gunObj.setName(results.getString("name"));
            gunObj.setPurchasePrice(results.getFloat("purchasePrice"));
            gunObj.setSalesPrice(results.getFloat("salesPrice"));
            gunObj.setRentPrice(results.getFloat("rentPrice"));
            gunObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
            gunObj.setMinStock(results.getInt("minStock"));
			gunObj.setMaxStock(results.getInt("maxStock"));
			gunObj.setCurrentStock(results.getInt("currentStock"));
			gunObj.setFabric(results.getString("fabric"));
			gunObj.setCalibre(results.getString("calibre"));
			gunObj.setSupplier(new Supplier(results.getInt("supplierID")));
        }
         catch(Exception e)
         {
         System.out.println("error in building the GunReplica object");
         }
    return gunObj;
    }















}

